#!/usr/bin/env python
# coding: utf-8

# In[1]:


get_ipython().system('pip install pymysql')


# **从数据库中导入爬取好的数据**

# In[2]:


import pymysql
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
get_ipython().run_line_magic('matplotlib', 'inline')
class mysql:#连接数据库的类
    db=pymysql.connect
    def __init__(self,url,username,pwd,dbname):
        self.db = pymysql.connect(url,username,pwd,dbname)#初始化连接
    def __del__(self):
        self.db.close()
    def do(self,sql):
        try:
            cursor = self.db.cursor()
            cursor.execute(sql)
            self.db.commit()
            return cursor.fetchall()
        except Exception:
            print(Exception)
a=mysql("markchen7788.xyz", "cq", "123456", "novel")#建立数据库连接对象
novels=pd.read_sql("select * from novel ",a.db)#读取所有爬取的小说集
a.__del__#释放资源
print(novels.describe())#查看dataframe数值量的统计情况


# **分别对三个网站爬取的数据进行清洗，主要包括去除重复数据、去除含有空值的数据、去除异常数据、统一三个网站小说的类型**

# In[16]:


no=novels.drop('id',axis=1)#去除id字段

no.drop_duplicates(inplace=True)#去除重复数据
'''
对飞卢网站的小说数据集进行清洗
'''

feilu=no[no['website']=='飞卢']
feilu=feilu.drop(['tuijian','jReview','jFavorite','jGifts','jPoint'],axis=1)#删除与飞卢小说网无关的字段
feilu=feilu.dropna()#去除含有空值的字段
feilu=feilu[feilu['zishu']>=3000]#去除字数少于3000的数据集

feilu.insert(6,'n_type',value='')#插入一个新字段

feilu.n_type=feilu.type#将原分类字段数据赋值给新分类字段

#根据需求将原有小说分类转换成统一的新分类
#都市言情、青春校园归为都市，科幻网游归为科幻
feilu.n_type[feilu['n_type']=='都市言情']='都市'
feilu.n_type[feilu['n_type']=='青春校园']='都市'

feilu.n_type[feilu['n_type']=='科幻网游']='科幻'

#将科幻类中标签里含有网游、NBA、明星、直播字眼的书归类为游戏竞技
feilu.n_type[(feilu['n_type']=='科幻')&(feilu['tag'].str.contains('网游'))]='游戏竞技'
feilu.n_type[(feilu['n_type']=='科幻')&(feilu['tag'].str.contains('NBA'))]='游戏竞技'
feilu.n_type[(feilu['n_type']=='科幻')&(feilu['tag'].str.contains('明星'))]='游戏竞技'
feilu.n_type[(feilu['n_type']=='科幻')&(feilu['tag'].str.contains('直播'))]='游戏竞技'

#将武侠仙侠类小说中标签含有武侠的小说归类为武侠，其余归类仙侠
feilu.n_type[feilu['n_type']=='武侠仙侠']='仙侠'
feilu.n_type[(feilu['n_type']=='仙侠')&(feilu['tag'].str.contains('武侠'))]='武侠'

#短篇其它归类为短篇，恐怖灵异归类为悬疑
feilu.n_type[feilu['n_type']=='短篇其他']='短篇'
feilu.n_type[feilu['n_type']=='恐怖灵异']='悬疑'

#将女生小说根据标签的关键字进一步划分为更细致的类型
feilu.n_type[feilu['n_type']=='女生小说']='女频言情'

feilu.n_type[(feilu['n_type']=='女频言情')&(feilu['tag'].str.contains('无限'))]='女频玄幻'
feilu.n_type[(feilu['n_type']=='女频言情')&(feilu['tag'].str.contains('玄幻'))]='女频玄幻'
feilu.n_type[(feilu['n_type']=='女频言情')&(feilu['tag'].str.contains('位面'))]='女频玄幻'
feilu.n_type[(feilu['n_type']=='女频言情')&(feilu['tag'].str.contains('异界'))]='女频玄幻'

feilu.n_type[(feilu['n_type']=='女频言情')&(feilu['tag'].str.contains('种田'))]='女频古代'
feilu.n_type[(feilu['n_type']=='女频言情')&(feilu['tag'].str.contains('穿越'))]='女频古代'
feilu.n_type[(feilu['n_type']=='女频言情')&(feilu['tag'].str.contains('权谋'))]='女频古代'


feilu.n_type[(feilu['n_type']=='女频言情')&(feilu['tag'].str.contains('修真'))]='女频仙侠'
feilu.n_type[(feilu['n_type']=='女频言情')&(feilu['tag'].str.contains('修仙'))]='女频仙侠'

feilu.n_type[(feilu['n_type']=='女频言情')&(feilu['tag'].str.contains('明星'))]='女频同人'

#统计新类型各种书籍的数量以及所占比例
aa=feilu.n_type.value_counts()
bb=aa*100/feilu.shape[0]

statistic=pd.DataFrame({'数量':aa,'占比%':bb})
print(statistic)


# In[17]:


'''
对晋江网站的小说数据集进行清洗
'''
jinjiang=no[no['website']=='晋江']
jinjiang=jinjiang.drop(['tuijian','yuepiao','dianji','dashang','xianhua'],axis=1)#删除与晋江小说网无关的字段
jinjiang=jinjiang.dropna()#去空值
jinjiang=jinjiang[jinjiang['zishu']>=500]#去除字数少于500字的小说

#插入“新类型“字段
jinjiang.insert(6,'n_type',value='女频言情')

#原有分类中含有短篇字眼的归类为短篇，含有悬疑、惊悚的归为悬疑
jinjiang.n_type[jinjiang['type'].str.contains('短篇')]='短篇'
jinjiang.n_type[jinjiang['type'].str.contains('悬疑')]='悬疑'
jinjiang.n_type[jinjiang['type'].str.contains('惊悚')]='悬疑'

#原有分类中含有仙侠、武侠的归为女频仙侠
jinjiang.n_type[jinjiang['type'].str.contains('仙侠')]='女频仙侠'
jinjiang.n_type[jinjiang['type'].str.contains('武侠')]='女频仙侠'

#含有架空历史的归类为女频玄幻，含有古色古香的归为女频古代
jinjiang.n_type[jinjiang['type'].str.contains('架空历史')]='女频玄幻'
jinjiang.n_type[jinjiang['type'].str.contains('古色古香')]='女频古代'

#含有衍生的归为女频同人
jinjiang.n_type[jinjiang['type'].str.contains('衍生')]='女频同人'

print(jinjiang.n_type.value_counts())


# In[18]:


'''
对起点网站的小说数据集进行清洗
'''
qidian=no[no['website']=='起点']
qidian=qidian.drop(['yuepiao','dianji','dashang','xianhua','jReview','jFavorite','jGifts','jPoint'],axis=1)#删除与起点小说网无关的字段
qidian=qidian.dropna()#去空值
qidian=qidian[qidian['zishu']>=1000]#去除字数少于1000字的小说


#插入“新类型“字段
qidian.insert(6,'n_type',value='')
#军事、历史合并
qidian.n_type[qidian['type']=='军事']='军事历史'
qidian.n_type[qidian['type']=='历史']='军事历史'

#都市、现实、现实生活合并为都市
qidian.n_type[qidian['type']=='都市']='都市'
qidian.n_type[qidian['type']=='现实']='都市'
qidian.n_type[qidian['type']=='现实生活']='都市'

#游戏、体育、游戏竞技合并为游戏竞技
qidian.n_type[qidian['type']=='游戏']='游戏竞技'
qidian.n_type[qidian['type']=='体育']='游戏竞技'
qidian.n_type[qidian['type']=='游戏竞技']='游戏竞技'

#科幻、科幻空间合并为科幻
qidian.n_type[qidian['type']=='科幻']='科幻'
qidian.n_type[qidian['type']=='科幻空间']='科幻'

qidian.n_type[qidian['type']=='武侠']='武侠'
qidian.n_type[qidian['type']=='仙侠']='仙侠'

#玄幻、奇幻合并
qidian.n_type[qidian['type']=='玄幻']='玄幻奇幻'
qidian.n_type[qidian['type']=='奇幻']='玄幻奇幻'

qidian.n_type[qidian['type']=='短篇']='短篇'

#悬疑、悬疑推理合并
qidian.n_type[qidian['type']=='悬疑']='悬疑'
qidian.n_type[qidian['type']=='悬疑推理']='悬疑'


qidian.n_type[qidian['type']=='玄幻言情']='女频玄幻'
qidian.n_type[qidian['type']=='古代言情']='女频古代'
qidian.n_type[qidian['type']=='仙侠奇缘']='女频仙侠'

#现代言情、浪漫青春合并为女频言情
qidian.n_type[qidian['type']=='现代言情']='女频言情'
qidian.n_type[qidian['type']=='浪漫青春']='女频言情'

#轻小说进一步细化
qidian.n_type[qidian['type']=='轻小说']='轻小说'

qidian.n_type[(qidian['type']=='轻小说')&(qidian['tag'].str.contains('衍生'))]='同人小说'

qidian.n_type[(qidian['type']=='轻小说')&(qidian['tag'].str.contains('同人衍生'))]='女频同人'
qidian.n_type[(qidian['type']=='轻小说')&(qidian['tag'].str.contains('古典衍生'))]='女频同人'
qidian.n_type[(qidian['type']=='轻小说')&(qidian['tag'].str.contains('唯美幻想'))]='女频同人'

print(qidian.n_type.value_counts())


# **将清洗好的数据持久化（存入数据库）**

# In[19]:


'''
将清洗好的飞卢网小说数据持久化（存入数据库）
'''
print(feilu.dtypes)
feilu.startTime=feilu.startTime.astype('str')#时间戳字段类型改为字符串
feilu.endTime=feilu.endTime.astype('str')
temp=feilu.values.tolist()#将dataframe转换为list
newdb=mysql("47.94.92.138", "cq", "123456", "_novel")
for i in temp:
    sql='insert into novel(name,zishu,zuozhe,website,type,tag,n_type,startTime,endTime,yuepiao,dianji,dashang,xianhua,yuanBookId) values("{}","{}","{}","{}","{}","{}","{}","{}","{}","{}","{}","{}","{}","{}")'.format(i[0],i[1],i[2],i[3],i[4],i[5],i[6],i[7],i[8],i[9],i[10],i[11],i[12],i[13])
    newdb.do(sql)
newdb.__del__


# In[8]:


'''
将清洗好的起点网小说数据持久化（存入数据库）
'''
qidian.startTime=qidian.startTime.astype('str')
qidian.endTime=qidian.endTime.astype('str')
temp=qidian.values.tolist()
newdb=mysql("47.94.92.138", "cq", "123456", "_novel")
for i in temp:
    sql='insert into novel(name,zishu,zuozhe,website,type,tag,n_type,startTime,endTime,tuijian,yuanBookId) values("{}","{}","{}","{}","{}","{}","{}","{}","{}","{}","{}")'.format(i[0],i[1],i[2],i[3],i[4],i[5],i[6],i[7],i[8],i[9],i[10])
    newdb.do(sql)
newdb.__del__


# In[9]:


'''
将清洗好的晋江网小说数据持久化（存入数据库）
'''
jinjiang.startTime=jinjiang.startTime.astype('str')
jinjiang.endTime=jinjiang.endTime.astype('str')
temp=jinjiang.values.tolist()
newdb=mysql("47.94.92.138", "cq", "123456", "_novel")
for i in temp:
    sql='insert into novel(name,zishu,zuozhe,website,type,tag,n_type,startTime,endTime,jReview,jFavorite,jGifts,jPoint,yuanBookId) values("{}","{}","{}","{}","{}","{}","{}","{}","{}","{}","{}","{}","{}","{}")'.format(i[0],i[1],i[2],i[3],i[4],i[5],i[6],i[7],i[8],i[9],i[10],i[11],i[12],i[13])
    #newdb.do(sql)
newdb.__del__

